package com.test.clients.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.RowMapper;

import com.test.clients.dao.ClientsDao;
import com.test.clients.dto.Client;
import com.test.dao.AbstractDao;
import com.test.dao.DBOperationResult;

public class ClientsDaoImpl extends AbstractDao implements ClientsDao {

	private static final String TABLE_NAME = "clients";

	private static final String SELECT =
		"select id, name"
		+ " from " + TABLE_NAME;

	private static final String ADD =
		"insert into " + TABLE_NAME + " (name) values (?)";

	private static final String UPDATE =
		"update " + TABLE_NAME + " set name = ?"
		+ " where id = ?";

	private static final String REMOVE =
		"delete from " + TABLE_NAME + " where id = ?";

	private final Map<String, String> propertyNameToDBColumnMap = new HashMap<String, String>();

	public ClientsDaoImpl() {
		propertyNameToDBColumnMap.put("id", "id");
		propertyNameToDBColumnMap.put("name", "name");
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#add(java.lang.Object)
	 */
	public DBOperationResult add(final Client newClient) {

		logger.debug("START add");

		try {
			final int rows = getSimpleJdbcTemplate().update(ADD, newClient.getName());
			logger.debug("Number of rows affected: " + rows);

			final String genId = getInsertedId(TABLE_NAME);
			newClient.setId(Integer.parseInt(genId));

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error adding client", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error adding client", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END add");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#update(java.lang.Object)
	 */
	public DBOperationResult update(final Client client) {

		logger.debug("START update");
		logger.debug("Client ID: " + client.getId());

		try {
			final int rows = getSimpleJdbcTemplate().update(UPDATE, client.getName(), client.getId());
			logger.debug("Affected rows: " + rows);

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error updating client", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error updating client", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END update");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#remove(java.lang.Object)
	 */
	public DBOperationResult remove(final Client client) {

		logger.debug("START remove");
		logger.debug("Client ID: " + client.getId());

		try {
			final int rows = getSimpleJdbcTemplate().update(REMOVE, client.getId());
			logger.debug("Affected rows: " + rows);

			return getResultForAffectedRows(rows);
		} catch (DataAccessException dae) {
			logger.error("SQL error removing client", dae);
			return getResultForDataAccessException(dae);
		} catch (Exception e) {
			logger.error("Generic error removing client", e);
			return DBOperationResult.ERROR_UNKNOWN;
		} finally {
			logger.debug("END remove");
		}
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#search(com.test.dao.SearchCriteria)
	 */
	public List<Client> search(final Map<String, Collection<? extends Object>> propertyCriteriaMap) {

		logger.debug("START search");

		final String whereCondition = buildWhereCondition(propertyCriteriaMap);
		final String sql = StringUtils.isEmpty(whereCondition) ? SELECT : SELECT + " where " + whereCondition;

		logger.debug("SQL: " + sql);

		List<Client> clients = null;
		try {
			clients = getSimpleJdbcTemplate().query(sql, new ClientMapper());
		} catch (DataAccessException dae) {
			logger.error("SQL exception while searching for clients", dae);
		} catch (Exception e) {
			logger.error("Generic error while searching for clients", e);
		} finally {
			logger.debug("Result size: " + clients == null ? "null" : clients.size());
			logger.debug("END search");
		}
		return clients;
	}

	/*
	 * (non-Javadoc)
	 * @see com.test.dao.Dao#load(java.lang.Integer)
	 */
	public Client load(final Integer pk) {

		logger.debug("START load");
		logger.debug("Primary key: " + pk);

		final String sql = SELECT
			+ " where id = ?";
		try {
			final List<Client> clients = getSimpleJdbcTemplate().query(sql, new ClientMapper(), pk);
			if (clients.size() == 0) {
				return null;
			} else {
				if (clients.size() > 1) {
					logger.warn("Multiple clients found for ID " + pk);
				}
				return clients.get(0);
			}
		} catch (DataAccessException dae) {
			logger.error("SQL exception when loading client with PK " + pk, dae);
			return null;
		} catch (Exception e) {
			logger.error("Generic error loading client with PK " + pk, e);
			return null;
		} finally {
			logger.debug("END load");
		}
	}

	/**
	 * Clients row mapper.
	 */
	private class ClientMapper implements RowMapper<Client> {

		public Client mapRow(final ResultSet rs, int row) throws SQLException {
			final Client client = new Client();
			client.setId(rs.getInt("id"));
			client.setName(rs.getString("name"));
			return client;
		}

	}

	@Override
	protected String mapPropertyNameToDBColumn(String propertyName) {
		return propertyNameToDBColumnMap.get(propertyName);
	}
}